﻿using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfApp.Models;
using System.IO;


namespace WpfApp.Services
{
    internal interface  IDataService
    {
        
        // 如果账号和密码正确则登录成功
        bool Login(string username, string password);
        // 注册，如果账号名已存在则返回错误,如果成功则自动创建“历史查询”，“我的生词本”两个单词本
        bool Register(string username, string password);
        // 根据用户名查找返回单词本列表，按创建时间排序
        List<Dictionary> WordBook(string username);
        // 根据用户名和单词本名称返回所有单词
        List<Word> GetWords(string username, string title);
        // 根据用户名和单词本名称返回score>=3的单词，按字母排序
        List<Word> GetLearnedWords(string username, string title);
        // 根据用户名和单词本名称返回score<3的单词，按score升序排序
        List<Word> GetUnLearnedWords(string username, string title);
        // 向某个单词本加入新的单词,单词本已经存在这个单词则不再加入
        bool AddWord(string username, string title, Word word);
        // 更改单词本中某个单词的熟练度
        bool ChangeWord(string username, string title, Word word);
        // 创建某个新的单词本
        bool AddWordBook(string username, string title, List<Word> book);
        // 删除单词本
        bool DeleteWordBook(string username, string title);
        // 单词本重命名
        bool RenameWordBook(string username, string oldTitle, string newTitle);

    }
     public class DataService: IDataService
    {
        private static readonly string BaseDirectory = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"..", "..","..", "Data")) ;
        private static readonly string DatabaseFileName = "register.db";
        private static readonly string DatabaseFilePath = Path.GetFullPath(Path.Combine(BaseDirectory,DatabaseFileName));
        private static readonly string ConnectionString = $"Data Source={DatabaseFilePath};Mode=ReadWriteCreate;";
        private static void Log(string message)
        {
            string logFilePath = Path.Combine(BaseDirectory, "logs", "log.txt");
            Directory.CreateDirectory(Path.GetDirectoryName(logFilePath)); // Ensure the directory exists
            using (StreamWriter writer = new StreamWriter(logFilePath, true))
            {
                writer.WriteLine($"{DateTime.Now}: {message}");
            }
        }
        public static SqliteConnection GetDatabaseConnection()
        {
            try
            {
                Log($"BaseDirectory: {BaseDirectory}");
                Log($"DatabaseFilePath: {DatabaseFilePath}");
                if (!File.Exists(DatabaseFilePath))
                {
                    throw new FileNotFoundException($"Database file not found at path: {DatabaseFilePath}");
                }

                var connection = new SqliteConnection(ConnectionString);
                connection.Open();
                return connection;
            }
            catch (Exception ex)
            {
                Log($"Failed to connect to the database file: {DatabaseFileName}. DatabaseFilePath: {DatabaseFilePath}");
                throw new Exception($" database file: {DatabaseFilePath}");
            }
        }


        // 登录方法
        public bool Login(string username, string password)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "SELECT COUNT(1) FROM register WHERE Username = @Username AND Password = @Password";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@Password", password);
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return count > 0;
                }
            }
        }
        // 注册方法
        public bool Register(string username, string password)
        {
            if (CheckUserExists(username))
            {
                return false;
            }
            else
            {
                RegisterUser(username, password);
                AddDefaultWordBooks(username);
                return true;
            }
        }

        private bool CheckUserExists(string username)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "SELECT COUNT(1) FROM register WHERE Username = @Username";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return count > 0;
                }
            }
        }

        private void RegisterUser(string username, string password)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "INSERT INTO register (Username, Password) VALUES (@Username, @Password)";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@Password", password);
                    command.ExecuteNonQuery();
                }
            }
        }

        private void AddDefaultWordBooks(string username)
        {
            AddWordBook(username, "历史查询", new List<Word>());
            AddWordBook(username, "我的生词本", new List<Word>());
        }

        // 获取单词本列表
        public List<Dictionary> WordBook(string username)
        {
            var wordBooks = new List<Dictionary>();

            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = @"
                    SELECT Name, 
                           (SELECT COUNT(*) FROM Words WHERE Username = @Username AND WordBook = WordBooks.Name) AS WordCount
                    FROM WordBooks 
                    WHERE Username = @Username 
                    ORDER BY CreatedAt";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            wordBooks.Add(new Dictionary
                            {
                                Title = reader.GetString(0),
                                WordCount = reader.GetInt32(1)
                            });
                        }
                    }
                }
            }

            return wordBooks;
        }

        // 获取所有单词
        public List<Word> GetWords(string username, string title)
        {
            return GetWordsByCondition(username, title, null, null);
        }

        // 获取已学单词
        public List<Word> GetLearnedWords(string username, string title)
        {
            return GetWordsByCondition(username, title, 3, true);
        }

        // 获取未学单词
        public List<Word> GetUnLearnedWords(string username, string title)
        {
            return GetWordsByCondition(username, title, 3, false);
        }

        private List<Word> GetWordsByCondition(string username, string title, int? scoreThreshold, bool? learned)
        {
            var words = new List<Word>();

            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "SELECT Word, Score, Meaning FROM Words WHERE Username = @Username AND WordBook = @Title";

                if (scoreThreshold.HasValue)
                {
                    query += learned.Value ? " AND Score >= @ScoreThreshold ORDER BY Word" : " AND Score < @ScoreThreshold ORDER BY Score";
                }

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@Title", title);
                    if (scoreThreshold.HasValue)
                    {
                        command.Parameters.AddWithValue("@ScoreThreshold", scoreThreshold.Value);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            words.Add(new Word
                            {
                                Name = reader.GetString(0),
                                Score = reader.GetInt32(1),
                                Meaning = JsonConvert.DeserializeObject<Dictionary<string, string>>(reader.GetString(2))
                            });
                        }
                    }
                }
            }

            return words;
        }

        // 添加单词
        public bool AddWord(string username, string title, Word word)
        {
            if (WordExists(username, title, word.Name))
            {
                return false;
            }

            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "INSERT INTO Words (Username, WordBook, Word, Score, Meaning) VALUES (@Username, @WordBook, @Word, @Score, @Meaning)";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@WordBook", title);
                    command.Parameters.AddWithValue("@Word", word.Name);
                    command.Parameters.AddWithValue("@Score", word.Score);
                    command.Parameters.AddWithValue("@Meaning", JsonConvert.SerializeObject(word.Meaning));
                    command.ExecuteNonQuery();
                }
            }

            return true;
        }

        // 更改单词熟练度
        public bool ChangeWord(string username, string title, Word word)
        {
            if (!WordExists(username, title, word.Name))
            {
                return false;
            }

            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "UPDATE Words SET Score = @Score WHERE Username = @Username AND WordBook = @WordBook AND Word = @Word";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@WordBook", title);
                    command.Parameters.AddWithValue("@Word", word.Name);
                    command.Parameters.AddWithValue("@Score", word.Score);
                    command.ExecuteNonQuery();
                }
            }

            return true;
        }

        // 添加单词本
        public bool AddWordBook(string username, string title, List<Word> book)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var transaction = connection.BeginTransaction();

                try
                {
                    var query = "INSERT INTO WordBooks (Username, Name, CreatedAt) VALUES (@Username, @Name, @CreatedAt)";

                    using (var command = new SqliteCommand(query, connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@Name", title);
                        command.Parameters.AddWithValue("@CreatedAt", DateTime.UtcNow);
                        command.ExecuteNonQuery();
                    }

                    foreach (var word in book)
                    {
                        var wordQuery = "INSERT INTO Words (Username, WordBook, Word, Score, Meaning) VALUES (@Username, @WordBook, @Word, @Score, @Meaning)";

                        using (var wordCommand = new SqliteCommand(wordQuery, connection, transaction))
                        {
                            wordCommand.Parameters.AddWithValue("@Username", username);
                            wordCommand.Parameters.AddWithValue("@WordBook", title);
                            wordCommand.Parameters.AddWithValue("@Word", word.Name);
                            wordCommand.Parameters.AddWithValue("@Score", word.Score);
                            wordCommand.Parameters.AddWithValue("@Meaning", JsonConvert.SerializeObject(word.Meaning));
                            wordCommand.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }

            return true;
        }

        // 删除单词本
        public bool DeleteWordBook(string username, string title)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var transaction = connection.BeginTransaction();

                try
                {
                    var query = "DELETE FROM WordBooks WHERE Username = @Username AND Name = @Name";

                    using (var command = new SqliteCommand(query, connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@Name", title);
                        command.ExecuteNonQuery();
                    }

                    var wordQuery = "DELETE FROM Words WHERE Username = @Username AND WordBook = @WordBook";

                    using (var wordCommand = new SqliteCommand(wordQuery, connection, transaction))
                    {
                        wordCommand.Parameters.AddWithValue("@Username", username);
                        wordCommand.Parameters.AddWithValue("@WordBook", title);
                        wordCommand.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }

            return true;
        }

        // 重命名单词本
        public bool RenameWordBook(string username, string oldTitle, string newTitle)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var transaction = connection.BeginTransaction();

                try
                {
                    var query = "UPDATE WordBooks SET Name = @NewName WHERE Username = @Username AND Name = @OldName";

                    using (var command = new SqliteCommand(query, connection, transaction))
                    {
                        command.Parameters.AddWithValue("@NewName", newTitle);
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@OldName", oldTitle);
                        command.ExecuteNonQuery();
                    }

                    var wordQuery = "UPDATE Words SET WordBook = @NewName WHERE Username = @Username AND WordBook = @OldName";

                    using (var wordCommand = new SqliteCommand(wordQuery, connection, transaction))
                    {
                        wordCommand.Parameters.AddWithValue("@NewName", newTitle);
                        wordCommand.Parameters.AddWithValue("@Username", username);
                        wordCommand.Parameters.AddWithValue("@OldName", oldTitle);
                        wordCommand.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }

            return true;
        }

        private bool WordExists(string username, string title, string word)
        {
            using (var connection = DataService.GetDatabaseConnection())
            {
                connection.Open();
                var query = "SELECT COUNT(1) FROM Words WHERE Username = @Username AND WordBook = @WordBook AND Word = @Word";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@WordBook", title);
                    command.Parameters.AddWithValue("@Word", word);
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return count > 0;
                }
            }
        }
    }
}

